package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Vector;

import modelo.LoteCompuesto;
import modelo.LoteSimple;
import modelo.Model;

public class AdmPersistenciaLoteCompuesto {

	private static AdmPersistenciaLoteCompuesto instancia;

	public static AdmPersistenciaLoteCompuesto getinstancia (){
		if(instancia == null)
			instancia = new AdmPersistenciaLoteCompuesto();
		return instancia;	
	}

	public Vector<LoteCompuesto> selectAll(){
		try{
			Vector<LoteCompuesto> resultados = new Vector<LoteCompuesto>();
			Connection connection = PoolConnection.getPoolConnecion().getConnection();
			Statement s = connection.createStatement();
			ResultSet rs = s.executeQuery("SELECT *  FROM Lote_Compuesto");
			int loteAnterior = rs.getInt(1);
			boolean cambioLote = false;
			Vector<LoteSimple> vls = new Vector<LoteSimple>();
			while(rs.next()){
				while(!cambioLote){


					if(loteAnterior == rs.getInt(1))
					{
						int nroLoteSimple = rs.getInt(2);
						LoteSimple ls = Model.getInstance().getLoteSimple(nroLoteSimple);
						vls.add(ls);
						loteAnterior = rs.getInt(1);
					}
					else{
						LoteCompuesto lc = new LoteCompuesto(loteAnterior,vls);
						vls.removeAllElements();
						resultados.add(lc);
						int nroLoteSimple = rs.getInt(2);
						LoteSimple ls = Model.getInstance().getLoteSimple(nroLoteSimple);
						vls.add(ls);
						loteAnterior = rs.getInt(1);
					}
				}
			}
			PoolConnection.getPoolConnecion().releaseConnection(connection);
			return resultados;	
		}
		catch (Exception e){
			System.out.println(e.getMessage());
		}
		return null; 
	}

	public void insert(LoteCompuesto l){
		try{
			Connection connection = PoolConnection.getPoolConnecion().getConnection();

			for(int i=0;i<l.getLotes().size();i++){
				PreparedStatement ps = connection.prepareStatement("INSERT INTO Lote_Compuesto (nroLote,nrolote_simple) values (?,?)");//revisar campos
				ps.setLong(1, l.getNroLote());
				ps.setLong(2, l.getLotes().get(i).getNroLote());
				ps.execute();
			}
			PoolConnection.getPoolConnecion().releaseConnection(connection);
		}
		catch (Exception e){
			System.out.println(e.getMessage());
		}
	}

	public void update(LoteCompuesto l){
		try{
			Connection connection = PoolConnection.getPoolConnecion().getConnection();
			PreparedStatement ps = connection.prepareStatement("DELETE FROM Lote_Compuesto " +
					"WHERE nroLote = ?,");//revisar campos
			ps.setLong(1, l.getNroLote());
			ps.execute();
			for(int i=0;i<l.getLotes().size();i++){
				PreparedStatement ps2 = connection.prepareStatement("INSERT INTO LotesCompuestos (nroLote,nrolote_simple) values (?,?)");//revisar campos
				ps2.setLong(1, l.getNroLote());
				ps2.setLong(2, l.getLotes().get(i).getNroLote());
				ps2.execute();
			}
			PoolConnection.getPoolConnecion().releaseConnection(connection);
		}
		catch (Exception e){
			System.out.println(e.getMessage());
		}
	}

	public void delete(LoteCompuesto l){
		try{
			Connection connection = PoolConnection.getPoolConnecion().getConnection();
			PreparedStatement ps = connection.prepareStatement("DELETE FROM Lote_Compuesto where nroLote = ?"); //revisar campos
			ps.setLong(1, l.getNroLote());
			ps.execute();
			PoolConnection.getPoolConnecion().releaseConnection(connection);
		}
		catch (Exception e){
			System.out.println(e.getMessage());
		}
	}
	
	public void delete(int nroLote){
		try{
			Connection connection = PoolConnection.getPoolConnecion().getConnection();
			PreparedStatement ps = connection.prepareStatement("DELETE FROM Lote_Compuesto where nroLote = ?"); //revisar campos
			ps.setLong(1, nroLote);
			ps.execute();
			PoolConnection.getPoolConnecion().releaseConnection(connection);
		}
		catch (Exception e){
			System.out.println(e.getMessage());
		}
	}

	public LoteCompuesto select(int nroLote){
		try{
			LoteCompuesto l = null;
			Connection connection = PoolConnection.getPoolConnecion().getConnection();
			PreparedStatement ps = connection.prepareStatement("SELECT * FROM Lote_Compuesto where nroLote = ?");
			ps.setInt(1, nroLote);
			ResultSet rs = ps.executeQuery();
			Vector<LoteSimple> vls = new Vector<LoteSimple>();
			while(rs.next()){
				int nroLoteSimple = rs.getInt(2);
				LoteSimple ls = Model.getInstance().getLoteSimple(nroLoteSimple);
				vls.add(ls);
			}
			l = new LoteCompuesto(nroLote,vls);
			PoolConnection.getPoolConnecion().releaseConnection(connection);
			return l;
		}
		catch (Exception e){
			System.out.println(e.getMessage());
		}
		return null;
	}
	
	public boolean existeLoteCompuesta(int nroLote) throws Exception{
		try{
			Connection connection = PoolConnection.getPoolConnecion().getConnection();
			PreparedStatement ps = connection.prepareStatement("SELECT * FROM Lote_Compuesto where nroLote = ?");
			ps.setInt(1, nroLote);
			ResultSet rs = ps.executeQuery();
			PoolConnection.getPoolConnecion().releaseConnection(connection);
			if(rs.getRow()==0)
				return false;
			else 
				return true;
			
			
			
		}
		catch (Exception e){
			System.out.println(e.getMessage());
			throw new Exception("No se pudo verificar");
		}
		
	}
}
